Modify Tables

While working with tables in Spread.Services, you can configure it as per your spreadsheet requirements by modifying the table using the properties and methods of the ITable interface.

Modify table range
Modify table areas
Modify totals row of table column

Modify table range

Spread.Services allows you to modify the table range of your worksheet using the Resize method of the ITable interface.

Refer to the following example code to modify table range.

C#	Copy Code
//Modify table range table.Resize(worksheet.Range["B1:E4"]);

Modify table areas

You can modify the value of specific table areas by accessing its header range, data range and total range using the HeaderRange property, DataRange property and TotalsRange property of the ITable interface.

Refer to the following example code to modify table areas in your worksheet.

C#	Copy Code
ITable table = worksheet.Tables.Add(worksheet.Range["A1:E5"], true); table.ShowTotals = true; //Populate table values worksheet.Range["A2"].Value = 3; worksheet.Range["A3"].Value = 4; worksheet.Range["A4"].Value = 2; worksheet.Range["A5"].Value = 1; worksheet.Range["B2"].Value = 32; worksheet.Range["B3"].Value = 41; worksheet.Range["B4"].Value = 12; worksheet.Range["B5"].Value = 16; worksheet.Range["C2"].Value = 3; worksheet.Range["C3"].Value = 4; worksheet.Range["C4"].Value = 15; worksheet.Range["C5"].Value = 18; //Table second column name set to "Age". worksheet.Tables[0].HeaderRange[0, 1].Value = "Age"; //"Age" Column's second row's value set to 23. worksheet.Tables[0].DataRange[1, 1].Value = 23; //"Age" column's total row function set to average. worksheet.Tables[0].TotalsRange[0, 1].Formula = "=SUBTOTAL(101,[Age])";

Copy Code

ITable table = worksheet.Tables.Add(worksheet.Range["A1:E5"], true);
table.ShowTotals = true;

//Populate table values           
worksheet.Range["A2"].Value = 3;
worksheet.Range["A3"].Value = 4;
worksheet.Range["A4"].Value = 2;
worksheet.Range["A5"].Value = 1;
worksheet.Range["B2"].Value = 32;
worksheet.Range["B3"].Value = 41;
worksheet.Range["B4"].Value = 12;
worksheet.Range["B5"].Value = 16;
worksheet.Range["C2"].Value = 3;
worksheet.Range["C3"].Value = 4;
worksheet.Range["C4"].Value = 15;
worksheet.Range["C5"].Value = 18;

//Table second column name set to "Age".
worksheet.Tables[0].HeaderRange[0, 1].Value = "Age";

//"Age" Column's second row's value set to 23.
worksheet.Tables[0].DataRange[1, 1].Value = 23;

//"Age" column's total row function set to average.
worksheet.Tables[0].TotalsRange[0, 1].Formula = "=SUBTOTAL(101,[Age])";

Modify totals row of table column

When you need to make changes to the total row's calculation function of a specific table column, you can use the TotalsCalculation property of the ITableColumn interface.

Refer to the following example code to modify column total row's calculation function.

C#	Copy Code
worksheet.Tables.Add(worksheet.Range["A1:C5"], true); worksheet.Tables[0].ShowTotals = true; //Populate table values worksheet.Range["A2"].Value = 3; worksheet.Range["A3"].Value = 4; worksheet.Range["A4"].Value = 2; worksheet.Range["A5"].Value = 1; worksheet.Range["B1"].Value = 13; worksheet.Range["B2"].Value = 32; worksheet.Range["B3"].Value = 41; worksheet.Range["B4"].Value = 12; worksheet.Range["B5"].Value = 16; worksheet.Range["C1"].Value = 1; worksheet.Range["C2"].Value = 3; worksheet.Range["C3"].Value = 4; worksheet.Range["C4"].Value = 15; worksheet.Range["C5"].Value = 18; //First table column's total row calculation fuction will be "=SUBTOTAL(101,[Column1])" worksheet.Tables[0].Columns[1].TotalsCalculation = TotalsCalculation.Count;

Copy Code

worksheet.Tables.Add(worksheet.Range["A1:C5"], true);
worksheet.Tables[0].ShowTotals = true;

//Populate table values           
worksheet.Range["A2"].Value = 3;
worksheet.Range["A3"].Value = 4;
worksheet.Range["A4"].Value = 2;
worksheet.Range["A5"].Value = 1;
worksheet.Range["B1"].Value = 13;
worksheet.Range["B2"].Value = 32;
worksheet.Range["B3"].Value = 41;
worksheet.Range["B4"].Value = 12;
worksheet.Range["B5"].Value = 16;
worksheet.Range["C1"].Value = 1;
worksheet.Range["C2"].Value = 3;
worksheet.Range["C3"].Value = 4;
worksheet.Range["C4"].Value = 15;
worksheet.Range["C5"].Value = 18;

//First table column's total row calculation fuction will be "=SUBTOTAL(101,[Column1])"
worksheet.Tables[0].Columns[1].TotalsCalculation = TotalsCalculation.Count;